package com.aopcinemas.cinema.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.aopcinemas.cinema.model.Hall;
import com.aopcinemas.cinema.model.Movie;
import com.aopcinemas.cinema.model.Movie.AgeConstraint;
import com.aopcinemas.cinema.model.Movie.Genre;
import com.aopcinemas.cinema.model.Schedule;
import com.aopcinemas.cinema.model.Session;
import com.aopcinemas.cinema.ontology.GetScheduleList;

public class ScheduleDao {

    private Connection connection;
    private SessionDao sessionDao;
    private HallDao hallDao;

    public ScheduleDao(Connection connection) {
        this.connection = connection;
        sessionDao = new SessionDao(connection);
        hallDao = new HallDao(connection);
    }

    public List<Schedule> getShedules() {
        List<Schedule> schedules = new ArrayList<Schedule>();

        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(//
                    "select " + //
                            "movie.id as movie_id, " + //
                            "movie.name as movie_name, " + //
                            "description, " + //
                            "age_constraint, " + //
                            "genre, " + //
                            "hall.id as hall_id, " + //
                            "hall.name as hall_name, " + //
                            "price, " + //
                            "start_time " + //
                            "from schedule, movie, hall " + //
                            "where schedule.movie_id = movie.id and schedule.hall_id = hall.id");
            while (rs.next()) {
                Schedule schedule = new Schedule();
                schedule.setId(rs.getLong("id"));
                schedule.setDate(rs.getTimestamp("start_time"));
                schedule.setBasePrice(rs.getDouble("price"));

                Hall hall = new Hall();
                hall.setId(rs.getLong("hall_id"));
                hall.setName(rs.getString("hall_name"));
                schedule.setHall(hall);

                Movie movie = new Movie();
                movie.setId(rs.getLong("movie_id"));
                movie.setName(rs.getString("movie_name"));
                movie.setGenre(Genre.valueOf(rs.getString("genre")));
                movie.setAgeConstraint(AgeConstraint.valueOf(rs.getString("age_constraint")));
                movie.setDescription(rs.getString("description"));
                schedule.setMovie(movie);
                schedules.add(schedule);
            }
            rs.close();
            st.close();
        } catch (SQLException e) {
            throw new RuntimeException("System error", e);
        }

        return schedules;
    }

    public List<Schedule> getShedules(GetScheduleList getScheduleList) {
        List<Schedule> schedules = new ArrayList<Schedule>();

        String sql = buildSql(getScheduleList);
        System.out.println("getShedules: " + sql);

        try {
            PreparedStatement st = connection.prepareStatement(sql);
            addParams(getScheduleList, st);
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Schedule schedule = new Schedule();
                schedule.setId(rs.getLong("id"));
                schedule.setDate(rs.getTimestamp("start_time"));
                schedule.setBasePrice(rs.getDouble("price"));

                Hall hall = new Hall();
                hall.setId(rs.getLong("hall_id"));
                hall.setName(rs.getString("hall_name"));
                schedule.setHall(hall);

                Movie movie = new Movie();
                movie.setId(rs.getLong("movie_id"));
                movie.setName(rs.getString("movie_name"));
                movie.setGenre(Genre.valueOf(rs.getString("genre")));
                movie.setAgeConstraint(AgeConstraint.valueOf(rs.getString("age_constraint")));
                movie.setDescription(rs.getString("description"));
                schedule.setMovie(movie);
                schedules.add(schedule);
            }
            rs.close();
            st.close();
        } catch (SQLException e) {
            throw new RuntimeException("System error", e);
        }

        return schedules;
    }

    private void addParams(GetScheduleList getScheduleList, PreparedStatement st) throws SQLException {
        int index = 1;
        if (getScheduleList.getStartTime() != null) {
            st.setTimestamp(index, new Timestamp(getScheduleList.getStartTime().getTime()));
            index++;
        }
        if (getScheduleList.getEndTime() != null) {
            st.setTimestamp(index, new Timestamp(getScheduleList.getEndTime().getTime()));
            index++;
        }
        if (getScheduleList.getMovie() != null) {
            Movie movie = getScheduleList.getMovie();
            if (movie.getName() != null) {
                st.setString(index, "%" + movie.getName() + "%");
                index++;
            }
            if (movie.getGenre() != null) {
                st.setString(index, movie.getGenre().toString());
                index++;
            }
            if (movie.getAgeConstraint() != null) {
                st.setString(index, movie.getAgeConstraint().toString());
                index++;
            }
        }
    }

    String buildSql(GetScheduleList getScheduleList) {
        String sql = "select " + //
                "movie.id as movie_id, " + //
                "movie.name as movie_name, " + //
                "age_constraint, " + //
                "genre, " + //
                "description, " + //
                "hall.id as hall_id, " + //
                "hall.name as hall_name, " + //
                "schedule.id as id, " + //
                "price, " + //
                "start_time " + //
                "from schedule, movie, hall " + //
                "where schedule.movie_id = movie.id and schedule.hall_id = hall.id";

        if (getScheduleList.getStartTime() != null) {
            sql = sql + " and start_time >= ? ";
        }
        if (getScheduleList.getEndTime() != null) {
            sql = sql + " and start_time <= ? ";
        }
        if (getScheduleList.getMovie() != null) {
            Movie movie = getScheduleList.getMovie();
            if (movie.getName() != null) {
                sql = sql + " and movie.name like ? "; // TODO: make it case
                                                       // insentitive
            }
            if (movie.getGenre() != null) {
                sql = sql + " and genre = ? ";
            }
            if (movie.getAgeConstraint() != null) {
                sql = sql + " and age_constraint = ? ";
            }
        }
        return sql;
    }

    public void addSchedule(Schedule schedule) {
        try {
            addScheduleInfo(schedule);
            addScheduleSessions(schedule);
        } catch (SQLException e) {
            throw new RuntimeException("System error", e);
        }
    }

    private void addScheduleSessions(Schedule schedule) {
        Hall hall = schedule.getHall();
        hallDao.getHall(hall);
        int seatRows = hall.getSeatRows().size();
        for (int r = 0; r < seatRows; r++) {
            int seats = hall.getSeatRows().get(r).getSeats().size();
            for (int s = 0; s < seats; s++) {
                Session session = new Session();
                session.setRow(r);
                session.setSeat(s);
                session.setSchedule(schedule);
                sessionDao.addSession(session);
            }
        }
    }

    private void addScheduleInfo(Schedule schedule) throws SQLException {
        CallableStatement ct = connection.prepareCall("call new_schedule(?,?,?,?,?)");
        ct.registerOutParameter(1, Types.BIGINT);
        ct.setLong(2, schedule.getMovie().getId());
        ct.setLong(3, schedule.getHall().getId());
        ct.setTimestamp(4, new Timestamp(schedule.getDate().getTime()));
        ct.setDouble(5, schedule.getBasePrice());
        ct.execute();
        schedule.setId(ct.getLong(1));
        ct.close();
    }

}
